#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

EVENT_LOG_CLEANED_SQL="
DROP TABLE IF EXISTS jtp_app_temp.temp_event_log_cleaned;
CREATE TABLE jtp_app_temp.temp_event_log_cleaned
AS SELECT
       ods_event_app_log.account,
       ods_event_app_log.appid,
       ods_event_app_log.appversion,
       ods_event_app_log.carrier,
       ods_event_app_log.deviceid,
       ods_event_app_log.devicetype,
       ods_event_app_log.eventid,
       ods_event_app_log.ip,
       ods_event_app_log.latitude,
       ods_event_app_log.longitude,
       ods_event_app_log.nettype,
       ods_event_app_log.osname,
       ods_event_app_log.osversion,
       ods_event_app_log.properties,
       ods_event_app_log.releasechannel,
       ods_event_app_log.resolution,
       ods_event_app_log.sessionid,
       ods_event_app_log.\`timestamp\`,
       dt
FROM jtp_app_ods.ods_event_app_log
WHERE dt = '${data_date}'
AND deviceid IS NOT NULL  AND length(deviceid) != 0
AND eventid IS NOT NULL  AND length(eventid) != 0
AND sessionid IS NOT NULL  AND length(sessionid) != 0
AND properties IS NOT NULL  AND size(properties) != 0;
"


EVENT_LOG_SESSION_SQL="
DROP TABLE IF EXISTS jtp_app_temp.tmp_event_log_session;
CREATE TABLE jtp_app_temp.tmp_event_log_session
AS
WITH
    temp1 AS (
        SELECT
            *
             , lag(\`timestamp\`, 1, 0) over (partition by sessionid order by \`timestamp\`) AS last_time_ts
             , (\`timestamp\` - lag(\`timestamp\`, 1, 0) over (partition by sessionid order by \`timestamp\`)) AS diff_ts
        FROM jtp_app_temp.temp_event_log_cleaned
    )
    , temp2 AS (
        SELECT
            *
             , if(diff_ts > 10 * 60 * 1000, concat(sessionid, '_', \`timestamp\`), null) AS start_session_id
        FROM temp1
    )
SELECT
    account, appid, appversion, carrier, deviceid, devicetype, eventid
    , ip, latitude, longitude, nettype, osname, osversion, properties
    , releasechannel, resolution, sessionid, \`timestamp\`, dt
    , last(start_session_id, true) over (partition by sessionid order by \`timestamp\`) AS new_session_id
FROM temp2;
"

EVENT_LOG_AREA_SQL="
DROP FUNCTION IF EXISTS convert_geohash;
CREATE FUNCTION convert_geohash AS 'net.bwie.jtp.app.udf.ConvertGeoHash'
    USING JAR 'hdfs://node101:8020/warehouse/app_jars/zg6-jtp-log-1.0-SNAPSHOT.jar';


DROP TABLE IF EXISTS jtp_app_temp.tmp_event_log_area;
CREATE TABLE jtp_app_temp.tmp_event_log_area
AS
SELECT
    t1.*
     , t2.province, t2.city, t2.district, t2.street
FROM jtp_app_temp.tmp_event_log_session t1
         LEFT JOIN jtp_app_dim.dim_md_areas t2 ON convert_geohash(t1.latitude, t1.longitude, 6) = t2.geo_hash
;
"

EVENT_LOG_DETAIL_SQL="
INSERT OVERWRITE TABLE jtp_app_dwd.dwd_event_log_detail PARTITION (dt='${data_date}')
SELECT
    account, appid, appversion, carrier, deviceid, devicetype, eventid
    , ip, latitude, longitude, province, city, district, nettype, osname
    , osversion, properties, releasechannel, resolution, new_session_id, \`timestamp\`
FROM jtp_app_temp.tmp_event_log_area
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${EVENT_LOG_CLEANED_SQL}${EVENT_LOG_SESSION_SQL}${EVENT_LOG_AREA_SQL}${EVENT_LOG_DETAIL_SQL}"

